DESIGNER /2000

Column definition using Designer/2000

By David C. Moss

In the previous two articles in our series on Designer/2000, we used the Data Diagrammer in Designer/2000 to examine and refine table, column, primary key, and foreign key definitions that we generated using the Database Design Wizard. In this article, we'll continue to add detail to our table definition through the Column Definition screen.

On your mark

To begin, you'll need to navigate to the Data Diagrammer by clicking the Data Diagrammer icon directly from Windows or by double-clicking the Designer/2000 Repository Object Navigator (RON) in Windows, then pulling down the Tools menu and selecting Data Diagrammer. Select a table box by double-clicking on it, and the Edit Table screen will appear. Now click on the Column Definition tab, depicted in Figure A, and follow along as we discuss the fields in the Column Definition screen.

Figure A

You enter column details about a table in the Column Definition tab of the Edit Table screen.

Let's get columnar

The Column Definition screen shown in Figure A allows you to enter details about each column within a table. If you've created attributes in Designer/2000 using Entity-Relationship modeling and have also used the Database Design Wizard, the column names and some of the details will already be filled in.

The Column Name field in Figure A shows a list of all the column names for the Customers table. Notice here that the column names aren't plural--they represent only one occurrence of information in each table--yet the table name is plural, because it represents many occurrences of Customer.

While Oracle allows you to use up to 30 characters for your column name, and you're certainly entitled to use all 30, long names can be cumbersome to type when you're developing PL/SQL. Yet it's also vitally important that the name of the column continue to have meaning, especially if you'll be designing the tables and columns, then handing them over to someone else at coding time. We recommend that you abbreviate column names using a list of standard abbreviations that your organization has created. If no such list exists, now would be the perfect time to start one.

In the Column Name field, you need to replace any blank spaces you had in your multi-word attributes with an underscore character: Thus, Country Code becomes Country_Code. This is done automatically if you use the Database Design Wizard to create tables and columns from entities and attributes. But if you're doing table design from scratch, which sometimes happens in a Rapid Application Development (RAD) approach, you probably won't have any entities or attributes. It's not that complicated, but if you haven't underscored your blanks, you can run into trouble during DDL generation.

Surrogate keys

At this point, we need to discuss a specific row in the Column Name field for our Customers table: Customer_ID. Customer_ID is a surrogate key, used to keep each occurrence of a customer unique in the database. Although the likelihood of someone having the same name, address, city, state, and zip is pretty slim, that's not the reason we need a surrogate key. The real reason is that it's simply a lot easier to work with Customer_ID alone as a foreign key in another table than it is to work with a half-dozen columns. Also, in real life it's quite possible that one of the users of the application you're designing will be unable to get all the details about a customer when initially taking information about the customer. Missing information in a multicolumn key means one thing: NO KEY! And no key means no saved row in the database. And no saved row means a very unhappy user.

The Seq field

The Seq (Sequence) field is used primarily as a number to order the columns when generating DDL. If you've used the Database Design Wizard to create tables and columns, the value for this field will be automatically created based on the importance of each column: primary key columns first, other Not Null columns second, and optional columns last. The column sequence is also used in the generation of Oracle Forms to determine order and placement of columns on the screen.

The Domain field

The Domain field allows you to specify a group from which your column will take its definition. Domains are particularly important if you need to have an environment where certain fields may later be combined or joined, or simply to enforce the consistency of certain types of fields in your database. If you defined entities and attributes in Designer/2000 and defined a domain for the attribute that a column was based on, the attribute's domain name will be copied into this field.

The Datatype field

The Datatype field allows you to specify how the information for a column will be stored in your database. The most commonly used data types are Varchar2, Number, and Date. You can choose from a number of other variations of each of these datatypes. For example, a more precise variation of Date is Timestamp, which stores date information down to the second. And Number has a multitude of variations, including Integer, Float, Real, and Decimal. It pays to be as precise as possible: Having an incorrect datatype will cause incorrect data to be entered.

The Opt field

The Opt field is used to indicate the optionality of the column. As in many other fields we've discussed in the Column Definition tab, this information will be automatically entered if you created your tables via the Database Design Wizard. If not, the default for this column is optional (Null), indicated by an X.

Speaking volumes

Many of the data entry screens in Designer/2000 have more than meets the eye: more fields, that is. As in many GUI applications, you can get to these additional fields in the Column Definition tab by using the horizontal scroll bar above the Insert Row and Delete Row buttons. Figure B shows the additional fields for this tab.

The Init Vol (Initial Volume) field is used to indicate the likelihood that data will be entered into a column. If the column is Not Null, as in the Customer_ID and Name fields, then the value will be 100. The Final Vol (Final Volumes) field indicates the likelihood of the column containing data when the row has reached a static state (i.e., when all of the data entry has been done for that row). This information is helpful in planning for table storage.

Figure B

Moving the horizontal bar on the Column Definition tab of the Edit Table screen reveals additional fields in which you can enter column details.

Additional fields

The Uppercase checkbox lets you indicate whether the data in this column should always be converted to uppercase letters. All uppercase can be helpful in some searches of the database when the application is up and running. The Create? checkbox lets you indicate whether the column will be included in the Create Table statement in your DDL. The Default Value field allows you to specify the value that will appear as a default when your DDL is created.

The Oracle Sequence field allows you to enter the name of a sequence that will be used to automatically generate the next available unique sequential number for a column. In the Customers table, the Customer_ ID column is the only one where an Oracle Sequence field could be used.

Next time, get keyed in

In our next article, we'll get into the details of defining and refining primary and foreign keys. We'll be spending time on the Constraints and Validations tabs in the Edit Table screen of the Data Diagrammer in Designer/2000.

Where to go for information

If you'd like to know more about Oracle's approach to database design, please refer to Appendix F of CASE*Method: Entity-Relationship Modelling by Richard Barker (Oracle/Addison-Wesley, 1989). Also, Oracle and other vendors offer a number of excellent classes on the subject of relational database design. We'd highly recommend that you find a relational database design expert to assist you through your first few designs.

David Moss is a managing consultant with TrueNorth Consulting, Inc., an information technology consultancy with offices in Portland, Seattle, and San Jose. He has worked with Oracle's CASE tools since 1988, including over three years with Oracle Consulting Group. You can reach David by phone at (503) 220-1790 or by E-mail at truenrth@ix.netcom.com.

[The Cobb Group Home Page]

Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.